import openpyxl
import re
from typing import Set, Union

def extract_all_numbers(ratio_value) -> Set[float]:
    """
    从持股比例值中提取所有可能的数字部分
    返回一个包含所有有效数字的集合
    """
    numbers = set()
    
    if ratio_value is None:
        return numbers
    
    # 如果已经是数字类型，直接返回
    if isinstance(ratio_value, (int, float)):
        numbers.add(float(ratio_value))
        return numbers
    
    # 转为字符串处理
    ratio_str = str(ratio_value).strip()
    
    # 使用多种分隔符分割字符串
    all_parts = []
    
    # 先尝试使用明显的分隔符
    for delimiter in [',', '，', ';', '；', '、', ' ', '\n', '\t']:
        if delimiter in ratio_str:
            parts = ratio_str.split(delimiter)
            all_parts.extend([p.strip() for p in parts if p.strip()])
    
    # 如果没有明显分隔符，添加整个字符串
    if not all_parts:
        all_parts.append(ratio_str)
    
    # 尝试从每个部分提取数字
    for part in all_parts:
        # 处理百分比格式 (例如 "25.5%")
        if '%' in part:
            try:
                cleaned = part.replace('%', '').strip()
                numbers.add(float(cleaned))
                continue
            except ValueError:
                pass
        
        # 使用正则表达式提取所有数字
        number_matches = re.findall(r'[\d\.]+', part)
        for num in number_matches:
            try:
                numbers.add(float(num))
            except ValueError:
                pass
    
    return numbers

def is_valid_shareholder_name(name: str) -> bool:
    """
    检查股东名称是否有效：
    - 不以字母或数字开头
    - 不以特殊字符（如\xa0）开头
    - 最好是中文开头
    """
    if not name or not isinstance(name, str):
        return False
    
    # 去除首尾空白
    name = name.strip()
    if not name:
        return False
    
    # 检查第一个字符
    first_char = name[0]
    
    # 排除以字母或数字开头的
    if re.match(r'[a-zA-Z0-9]', first_char):
        return False
    
    # 排除以特殊空白字符开头的
    if first_char in ('\xa0', '\u3000', ' ', '\t'):
        return False
    
    return True

def format_amount(amount: Union[str, int, float]) -> str:
    """
    处理认缴出资额格式：
    1. 保持原始字符串格式不变
    2. 如果是数字类型，转为字符串时不进行四舍五入
    """
    if amount is None:
        return None
    
    if isinstance(amount, (int, float)):
        # 对于整数直接转为字符串
        if isinstance(amount, int) or amount.is_integer():
            return f"{int(amount)}万(元)"
        # 对于浮点数，使用repr保持完整精度
        return f"{repr(amount).rstrip('0').rstrip('.') if '.' in repr(amount) else repr(amount)}万(元)"
    
    return str(amount)

def read_excel_with_merged_cells_shareholder(file_path):
    """功能：爱企查-股东数据excel处理（合并单元格处理）"""
    # 加载工作簿
    wb = openpyxl.load_workbook(file_path)
    sheet = wb.worksheets[0]
    
    # 获取表头
    headers = [cell.value for cell in sheet[1]]
    
    # 存储处理后的数据
    processed_data = []
    current_shareholder = None
    
    # 遍历行（跳过表头）
    for row in sheet.iter_rows(min_row=2, values_only=True):
        if row[0] is not None:  # 新股东
            if current_shareholder is not None:
                processed_data.append(current_shareholder)
            
            current_shareholder = {
                '序号': row[0],
                '股东名称': row[1] if is_valid_shareholder_name(row[1]) else None,
                '持股比例': extract_all_numbers(row[2]),
                '认缴出资额': format_amount(row[3]),
                '认缴出资日期': row[4]
            }
        else:  # 合并单元格部分
            if row[1] and is_valid_shareholder_name(row[1]):
                if (current_shareholder['股东名称'] is None or 
                    len(str(row[1])) > len(str(current_shareholder['股东名称']))):
                    current_shareholder['股东名称'] = row[1]
            
            if row[2] is not None:
                current_numbers = extract_all_numbers(row[2])
                if current_numbers:
                    current_shareholder['持股比例'].update(current_numbers)
            
            if row[3] is not None:
                current_shareholder['认缴出资额'] = format_amount(row[3])
            
            if row[4] is not None:
                current_shareholder['认缴出资日期'] = row[4]
    
    # 处理最后一个股东
    if current_shareholder is not None:
        processed_data.append(current_shareholder)
    
    # 转换为列表格式
    result = [headers]
    for item in processed_data:
        ratio_str = ', '.join(
            # 持股比例保持原有处理方式
            f"{num:.4f}".rstrip('0').rstrip('.') if '.' in f"{num:.4f}" else f"{num}"
            for num in sorted(item['持股比例'])
        ) if item['持股比例'] else None
        
        result.append([
            item['序号'],
            item['股东名称'],
            ratio_str,
            item['认缴出资额'],  # 这里已经是处理过的字符串
            item['认缴出资日期']
        ])
    
    return result

def test_read_excel_with_merged_cells():
    # 使用示例
    file_path = "爱企查-信诺金智-股权结构.xlsx"
    # file_path = "爱企查-中科昊宇-股权结构.xlsx"
    excel_data = read_excel_with_merged_cells_shareholder(file_path)

    # 打印读取的数据
    for row in excel_data:
        print(row)

if __name__ == "__main__":
    test_read_excel_with_merged_cells()